import json
import pymysql
import requests

# 香港残骸清除责任保险或其他财务保证证书签发数量、残骸清除责任保险或其他财务保证证书签发数量
BASE_URL = 'https://zwfw.sz.msa.gov.cn/manage/windowapply/getDeptDone?applyStatus=&approveOrgCode=&isCheck=1&mechanism=01020214&applyNum=&affairName=%E6%AE%8B%E9%AA%B8%E6%B8%85%E9%99%A4%E8%B4%A3%E4%BB%BB%E4%BF%9D%E9%99%A9%E6%88%96%E5%85%B6%E4%BB%96%E8%B4%A2%E5%8A%A1%E4%BF%9D%E8%AF%81%E8%AF%81%E4%B9%A6%E7%AD%BE%E5%8F%91&applicant=&vVesselName=&serveBusiness=&applyStartTime=2024-10-01&applyEndTime=2024-12-31&acceptStartTime=&acceptEndTime=&size=450&current=1'
GET_INFO_URL = 'https://zwfw.sz.msa.gov.cn/manage/shipOilDamageInsuranceCert/getByPrimaryKey1/'

# 请求头部
headers = {
    'Accept': 'application/json, text/plain, */*',
    'Authorization': 'Bearer 3eaf7358-959a-4411-b2a5-c923db5c1425',
    'content-type': 'application/json;charset=UTF-8'
}
# 账号身份token
cookies = {
    'access_token': 'eyJhbGciOiJIUzI1NiJ9.eyJpYXQiOjE3NDA5ODkxNjUsImV4cCI6MTc0MTAxNzk2NSwic3ViIjoie1xuICBcInVzZXJJZFwiIDogXCIxMDUzMTU1NzY4LTAxMDIwMjE0MDEwMjEyMDQwNzA5XCIsXG4gIFwicGFzc3dvcmRcIiA6IFwiJDJhJDEwJENpb3B5MW1QUUxUWjluU0lKUWNGWi55Zy42UUMyL0F3THdva1QySmNjRFZrakhiSW43UGZTXCIsXG4gIFwidXNlckNvZGVcIiA6IFwiNDQ1MjIyMTk5MTA4MDIyNDc5LTAxMDIwMjE0MDEwMjEyMDQwNzA5XCIsXG4gIFwidXNlck5hbWVDblwiIDogXCLmm77ojILov5tcIixcbiAgXCJjbGllbnRJcFwiIDogXCJcIixcbiAgXCJhcHBJZFwiIDogXCJcIixcbiAgXCJhcHBDb2RlXCIgOiBcIkNUVElDXCIsXG4gIFwib2ZmaWNlSWRcIiA6IFwiNjEyQkMwQjgyQzMyNDg0RjlBRDk0Q0RENURBN0FDOUZcIixcbiAgXCJzeXNSb2xlSWRcIiA6IG51bGwsXG4gIFwib2ZmaWNlTmFtZVwiIDogXCLmt7HlnLPmtbfkuovlsYBcIixcbiAgXCJyb2xlSWRzXCIgOiBbIFwiMjYwMDAxXCIsIFwiMjYwMDAzXCIgXSxcbiAgXCJwYXNzd29yZE1vZGlmeVRpbWVcIiA6IG51bGwsXG4gIFwiaXNQYXNzd29yZE1vZGlmeVwiIDogbnVsbCxcbiAgXCJ1c2VybmFtZVwiIDogXCI0NDUyMjIxOTkxMDgwMjI0NzktMDEwMjAyMTQwMTAyMTIwNDA3MDlcIixcbiAgXCJkZXB0T2ZmaWNlQ29kZVwiIDogbnVsbCxcbiAgXCJkZXB0T2ZmaWNlTmFtZVwiIDogbnVsbCxcbiAgXCJzZXR0bGVPZmZpY2VDb2RlXCIgOiBudWxsLFxuICBcInNldHRsZU9mZmljZU5hbWVcIiA6IG51bGwsXG4gIFwicm9sZU9mZmljZU5hbWVcIiA6IG51bGwsXG4gIFwicm9sZU9mZmljZUNvZGVcIiA6IG51bGwsXG4gIFwicm9sZU9mZmljZUlkXCIgOiBudWxsLFxuICBcIm9mZmljZUNvZGVcIiA6IFwiMDEwMjAyMTRcIixcbiAgXCJ1c2VyQ29va2llS2V5XCIgOiBcIjEwNTMxNTU3NjgtMDEwMjAyMTQwMTAyMTIwNDA3MDlfQ1RUSUNcIixcbiAgXCJkYXRhQXV0aG9yaXR5SWRcIiA6IG51bGwsXG4gIFwicHJpbmNpcGFsT3JnQ29kZVwiIDogXCLmt7HlnLPmtbfkuovlsYBcIixcbiAgXCJvZmZpY2VUeXBlXCIgOiBudWxsLFxuICBcIm1hbmFnZVR5cGVcIiA6IG51bGwsXG4gIFwibG9naW5UaW1lXCIgOiBudWxsLFxuICBcInN5c3RlbVJhdGVcIiA6IG51bGwsXG4gIFwiaG9tZUN1cnJlbmN5XCIgOiBudWxsLFxuICBcIm9mZmljZU1kbUlkXCIgOiBudWxsLFxuICBcImNvcnBNZG1JZFwiIDogbnVsbCxcbiAgXCJjb3JwU2hvcnRDb2RlXCIgOiBudWxsLFxuICBcInN5c1JvbGVMaXN0XCIgOiBudWxsLFxuICBcInVzZXJuYW1lSWFtYlwiIDogbnVsbCxcbiAgXCJ1c2VySWRJYW1iXCIgOiBudWxsLFxuICBcImR1dHlJZFwiIDogXCJBQ0ZEOTZFRjAyNjA0RUM5OTEyMzM3QjIzNTIzRjgxMlwiLFxuICBcImR1dHlOYW1lXCIgOiBcIuWkluiBmOaZruWylzlcIixcbiAgXCJkdXR5Q29kZVwiIDogXCIwMTAyMDIxNDAxMDIxMjA0MDcwOVwiLFxuICBcImF1dGhvcml0aWVzXCIgOiBbIF0sXG4gIFwiZW5hYmxlZFwiIDogZmFsc2UsXG4gIFwiYWNjb3VudE5vbkV4cGlyZWRcIiA6IGZhbHNlLFxuICBcImFjY291bnROb25Mb2NrZWRcIiA6IGZhbHNlLFxuICBcImNyZWRlbnRpYWxzTm9uRXhwaXJlZFwiIDogZmFsc2Vcbn0ifQ.tS3x7TVMy04yxrfFrGorZUqEbbYJ88gYD5s8xTwJvR4',
    'msa_token': '3eaf7358-959a-4411-b2a5-c923db5c1425'
}
# 组装请求配置
session = requests.Session()
response_lo = session.get(BASE_URL, headers=headers, cookies=cookies)
# 响应回来的参数
print("响应回来的参数:",response_lo.text)
# 响应回来的参数文本转json
strData = json.loads(response_lo.text)
# 获取到具体数据的位置
strRecordsData = strData['data']['records']

# 获取数据情况records、total、size、current、pages
print("获取："+str(strData['data']['total'])+"条数据","获取："+str(strData['data']['pages'])+"页数据","获取每页设置"+str(strData['data']['size'])+"条数据")

# 打印每一列数据的字段key
print("获取建表字段：", tuple(strRecordsData[0].keys()))

if response_lo.status_code == 200:
    # 数据库连接配置
    db_config = {
        'host': '191.254.8.220',
        'user': 'root',
        'password': 'Szmsa@520',
        'db': 'spiders',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }
    # 表名和字段名
    table_name = 'ywtg_oa_ship_wreckage_clear_duty'
    table_accept = 'ywtg_oa_ship_wreckage_clear_duty_acceptor'
    table_review = 'ywtg_oa_ship_wreckage_clear_duty_auditor'
    # 连接数据库
    connection = pymysql.connect(**db_config)

    try:
        # 开始事务
        with connection.cursor() as cursor:
            # 循环插入每条记录
            for record in strRecordsData:
                # 获取需要插入的字段名称
                fields = ', '.join(record.keys())
                # 判断是否包含 "index"，如果包含则替换为 "index1"
                if "index" in fields:
                    fields = fields.replace("index", "index1")
                # 拼装需要插入字段对应的%号个数
                values = ", ".join(['%s'] * len(record))
                # 判断是否包含 "None"，如果包含则替换为 "null"
                if "None" in values:
                    values = values.replace("None", "null")
                # print("!!!"+fields)
                # 查详情的windowApplyId
                windowApply_id = record['windowApplyId']
                # print("查详情的windowApplyId:",windowApply_id)

                # 构建插入语句，插入业务主表
                insert_stmt = (
                    "INSERT INTO `{table}` ({fields}) "
                    "VALUES ({values})"
                ).format(
                    table=table_name,
                    fields=fields,
                    values=values
                )
                # 插入语句
                #print("插入SQl语句：", insert_stmt)
                #print("插入值：", tuple(record.values()))
                # 执行sql语句插入数据，record.values()用于获取字段中的值
                cursor.execute(insert_stmt, tuple(record.values()))
                response_auditor = session.get(GET_INFO_URL + windowApply_id, headers=headers, cookies=cookies)
                auditorDataText = json.loads(response_auditor.text)
                print("获取受理人详情:", auditorDataText['data']['accept'])
                print("获取审查人详情:", auditorDataText['data']['review'])

                auditorList = []
                # 如果受理人列表为空
                if auditorDataText['data']['accept'] is not None:
                    # 因为受理人信息不是列表，所以要转换一下格式
                    auditorList.append(auditorDataText['data']['accept'])
                else:
                    auditorList = []  # 如果结构不符合预期，设置为空列表

                reviewList = []
                # 如果审核人列表为空
                if auditorDataText['data']['review'] is not None:
                    # 因为审核人信息不是列表，所以要转换一下格式
                    reviewList.append(auditorDataText['data']['review'])
                else:
                    reviewList = []  # 如果结构不符合预期，设置为空列表

                # 获取的成功状态
                if response_auditor.status_code == 200:

                    # 循环插入受理人信息
                    for auditor in auditorList:
                        # 获取需要插入的字段名称
                        auditor_fields = ', '.join(auditor.keys())
                        print("获取受理人表格字段:", auditor.keys())
                        # 拼装需要插入字段对应的%号个数
                        auditor_values = ", ".join(['%s'] * len(auditor))
                        insert_auditor = (
                            "INSERT INTO `{table}` ({fields}) "
                            "VALUES ({values})"
                        ).format(
                            table=table_accept,
                            fields=auditor_fields,
                            values=auditor_values
                        )
                        cursor.execute(insert_auditor, tuple(auditor.values()))

                    # 循环插入审核人信息
                    for review in reviewList:
                        # 获取需要插入的字段名称
                        review_fields = ', '.join(review.keys())
                        print("获取审查人表格字段:", review.keys())
                        # 拼装需要插入字段对应的%号个数
                        review_values = ", ".join(['%s'] * len(review))
                        insert_review = (
                            "INSERT INTO `{table}` ({fields}) "
                            "VALUES ({values})"
                        ).format(
                            table=table_review,
                            fields=review_fields,
                            values=review_values
                        )
                        cursor.execute(insert_review, tuple(review.values()))
        # 提交事务
        connection.commit()
    finally:
        # 关闭数据库连接
        connection.close()
